package com.youhp.spire.xls.demo.PivotTable;

import com.spire.xls.*;

public class CreatePivotTable {
    public static void main(String[] args) {
        //Create a workbook.
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Set the value of the cells.
        sheet.getCellRange("A1").setValue("Product");
        sheet.getCellRange("B1").setValue("Month");
        sheet.getCellRange("C1").setValue("Count");

        sheet.getCellRange("A2").setValue("SpireDoc");
        sheet.getCellRange("A3").setValue("SpireDoc");
        sheet.getCellRange("A4").setValue("SpireXls");
        sheet.getCellRange("A5").setValue("SpireDoc");
        sheet.getCellRange("A6").setValue("SpireXls");
        sheet.getCellRange("A7").setValue("SpireXls");

        sheet.getCellRange("B2").setValue("January");
        sheet.getCellRange("B3").setValue("February");
        sheet.getCellRange("B4").setValue("January");
        sheet.getCellRange("B5").setValue("January");
        sheet.getCellRange("B6").setValue("February");
        sheet.getCellRange("B7").setValue("February");

        sheet.getCellRange("C2").setValue("10");
        sheet.getCellRange("C3").setValue("15");
        sheet.getCellRange("C4").setValue("9");
        sheet.getCellRange("C5").setValue("7");
        sheet.getCellRange("C6").setValue("8");
        sheet.getCellRange("C7").setValue("10");

        //Add a PivotTable to the worksheet.
        CellRange dataRange = sheet.getCellRange("A1:C7");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);
        PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getCellRange("E10"), cache);

        //Drag the fields to the row area.
        PivotField pf =null;
        if (pt.getPivotFields().get("Product") instanceof PivotField){
            pf= (PivotField) pt.getPivotFields().get("Product");
        }
        pf.setAxis(AxisTypes.Row);

        PivotField pf2 =null;
        if (pt.getPivotFields().get("Month") instanceof PivotField){
            pf2= (PivotField) pt.getPivotFields().get("Month");
        }
        pf2.setAxis(AxisTypes.Row);

        //Drag the field to the data area.
        pt.getDataFields().add(pt.getPivotFields().get("Count"), "SUM of Count", SubtotalTypes.Sum);

        //Set PivotTable style.
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);

        //Auto fit columns generated by the pivotTable.
        pt.calculateData();
        sheet.autoFitColumn(5);
        sheet.autoFitColumn(6);

        //Save to file.
        workbook.saveToFile("output/CreatePivotTable.xlsx", ExcelVersion.Version2013);
    }
}
